﻿Imports System.Data.SqlClient
Public Class frmGestionCheque
    Dim lstViewItem As ListViewItem
    Dim par As String = ""

    Private Sub txtValeur_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txtAnnee.KeyPress, txtMois.KeyPress, txtJour.KeyPress
        Dim allowedChars As String = "1234567890"
        If e.KeyChar = ChrW(8) Then
            e.Handled = False
        ElseIf allowedChars.IndexOf(e.KeyChar) = -1 Then
            ' Invalid Character
            e.Handled = True
        End If
    End Sub

    Private Sub cmbPar_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs)
        txtAnnee.Clear()
    End Sub

    Private Sub btnValider_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnValider.Click
        Try
            If Not String.IsNullOrEmpty(txtAnnee.Text) And Not String.IsNullOrWhiteSpace(txtAnnee.Text) _
                    Or Not String.IsNullOrEmpty(txtMois.Text) And Not String.IsNullOrWhiteSpace(txtMois.Text) _
                    Or Not String.IsNullOrEmpty(txtJour.Text) And Not String.IsNullOrWhiteSpace(txtJour.Text) Then
                If cmbTypePaiement.SelectedIndex = 0 Then

                    DataGridView1.Rows.Clear()
                    Dim str As String = ""
                    If cmbEtat.SelectedIndex = 2 Then
                        str = " and dteRetra is NULL"
                    ElseIf cmbEtat.SelectedIndex = 1 Then
                        str = " and dteRetra is not NULL"
                    End If

                    If Not String.IsNullOrEmpty(txtAnnee.Text) And Not String.IsNullOrWhiteSpace(txtAnnee.Text) Then
                        par = "YEAR(dateChequ) = " & txtAnnee.Text
                    End If
                    If Not String.IsNullOrEmpty(txtMois.Text) And Not String.IsNullOrWhiteSpace(txtMois.Text) Then
                        par += " and month(dateChequ) = " & txtMois.Text
                    End If
                    If Not String.IsNullOrEmpty(txtJour.Text) And Not String.IsNullOrWhiteSpace(txtJour.Text) Then
                        par += " and day(dateChequ) = " & txtJour.Text
                    End If

                    Cmd = New SqlCommand("select * from cheques where " & par & str & " and typePersonne not like 'C'", Con)

                    Dim cmpLigne As Integer = 0
                    Dim cmpColone As Integer = 0
                    open()
                    Dr = Cmd.ExecuteReader
                    If Dr.HasRows Then
                        While Dr.Read()
                            DataGridView1.Rows.Add(New DataGridViewRow)
                            For cmpColone = 0 To Dr.FieldCount - 1
                                If cmpColone = 3 Then
                                    If convertNullToDate(Dr(cmpColone)) = "01-01-0001" Then
                                        DataGridView1.Rows(cmpLigne).Cells(cmpColone).Value = ""
                                    Else
                                        DataGridView1.Rows(cmpLigne).Cells(cmpColone).Value = Format(Dr(cmpColone), "dd-MM-yyyy")
                                    End If
                                ElseIf cmpColone = 4 Then
                                    DataGridView1.Rows(cmpLigne).Cells(cmpColone).Value = Format(Dr(cmpColone), "dd-MM-yyyy")
                                ElseIf cmpColone = 2 Then
                                    If Dr.GetBoolean(2) = True Then
                                        DataGridView1.Rows(cmpLigne).Cells(cmpColone).Value = "Oui"
                                    Else
                                        DataGridView1.Rows(cmpLigne).Cells(cmpColone).Value = "Non"
                                    End If
                                Else
                                    DataGridView1.Rows(cmpLigne).Cells(cmpColone).Value = Dr(cmpColone)
                                End If
                            Next

                            'With cheq
                            '    .idPai = Dr.GetInt32(0)
                            '    .numChe = Dr.GetString(1)
                            '    .nominatif = Dr.GetBoolean(2)
                            '    .dteRetra = Dr(3)
                            '    .dateChequ = Dr(4)
                            '    .banque = Dr(5)
                            'End With
                            cmpLigne += 1
                        End While
                    End If
                ElseIf cmbTypePaiement.SelectedIndex = 1 Then
                    DataGridView1.Rows.Clear()
                    Dim str As String = ""
                    If cmbEtat.SelectedIndex = 2 Then
                        str = " and dteRetrait is NULL"
                    ElseIf cmbEtat.SelectedIndex = 1 Then
                        str = " and dteRetrait is not NULL"
                    End If

                    If Not String.IsNullOrEmpty(txtAnnee.Text) And Not String.IsNullOrWhiteSpace(txtAnnee.Text) Then
                        par = "YEAR(dteEffet) = " & txtAnnee.Text
                    End If
                    If Not String.IsNullOrEmpty(txtMois.Text) And Not String.IsNullOrWhiteSpace(txtMois.Text) Then
                        par += " and month(dteEffet) = " & txtMois.Text
                    End If
                    If Not String.IsNullOrEmpty(txtJour.Text) And Not String.IsNullOrWhiteSpace(txtJour.Text) Then
                        par += " and day(dteEffet) = " & txtJour.Text
                    End If

                    Cmd = New SqlCommand("select * from Effet where " & par & str & " and typePersonne not like 'C'", Con)

                    Dim cmpLigne As Integer = 0
                    Dim cmpColone As Integer = 0
                    open()
                    Dr = Cmd.ExecuteReader
                    If Dr.HasRows Then
                        While Dr.Read()
                            DataGridView1.Rows.Add(New DataGridViewRow)
                            For cmpColone = 0 To Dr.FieldCount - 1
                                If cmpColone = 3 Then
                                    If convertNullToDate(Dr(cmpColone)) = "01-01-0001" Then
                                        DataGridView1.Rows(cmpLigne).Cells(cmpColone).Value = ""
                                    Else
                                        DataGridView1.Rows(cmpLigne).Cells(cmpColone).Value = Format(Dr(cmpColone), "dd-MM-yyyy")
                                    End If
                                ElseIf cmpColone = 4 Then
                                    DataGridView1.Rows(cmpLigne).Cells(cmpColone).Value = Format(Dr(cmpColone), "dd-MM-yyyy")
                                ElseIf cmpColone = 2 Then
                                    DataGridView1.Rows(cmpLigne).Cells(cmpColone).Value = Format(Dr(cmpColone), "dd-MM-yyyy")
                                ElseIf cmpColone = 5 Then
                                    If Dr.GetBoolean(5) = True Then
                                        DataGridView1.Rows(cmpLigne).Cells(cmpColone).Value = "Oui"
                                    Else
                                        DataGridView1.Rows(cmpLigne).Cells(cmpColone).Value = "Non"
                                    End If
                                Else
                                    DataGridView1.Rows(cmpLigne).Cells(cmpColone).Value = Dr(cmpColone)
                                End If
                            Next

                            'With cheq
                            '    .idPai = Dr.GetInt32(0)
                            '    .numChe = Dr.GetString(1)
                            '    .nominatif = Dr.GetBoolean(2)
                            '    .dteRetra = Dr(3)
                            '    .dateChequ = Dr(4)
                            '    .banque = Dr(5)
                            'End With
                            cmpLigne += 1
                        End While
                    End If
                Else
                    DataGridView1.Rows.Clear()
                    Dim str As String = ""
                    If cmbEtat.SelectedIndex = 2 Then
                        str = " and dateVirement > GETDATE()"
                    ElseIf cmbEtat.SelectedIndex = 1 Then
                        str = " and dateVirement <= GETDATE()"
                    End If

                    If Not String.IsNullOrEmpty(txtAnnee.Text) And Not String.IsNullOrWhiteSpace(txtAnnee.Text) Then
                        par = "YEAR(dateVirement) = " & txtAnnee.Text
                    End If
                    If Not String.IsNullOrEmpty(txtMois.Text) And Not String.IsNullOrWhiteSpace(txtMois.Text) Then
                        par += " and month(dateVirement) = " & txtMois.Text
                    End If
                    If Not String.IsNullOrEmpty(txtJour.Text) And Not String.IsNullOrWhiteSpace(txtJour.Text) Then
                        par += " and day(dateVirement) = " & txtJour.Text
                    End If

                    Cmd = New SqlCommand("select * from virement where " & par & str & " and typePersonne not like 'C'", Con)
                    Dim cmpLigne As Integer = 0
                    Dim cmpColone As Integer = 0
                    open()
                    Dr = Cmd.ExecuteReader
                    If Dr.HasRows Then
                        While Dr.Read()
                            DataGridView1.Rows.Add(New DataGridViewRow)
                            For cmpColone = 0 To Dr.FieldCount - 1
                                If cmpColone = 4 Then
                                    DataGridView1.Rows(cmpLigne).Cells(cmpColone).Value = Format(Dr(cmpColone), "dd-MM-yyyy")
                                Else
                                    DataGridView1.Rows(cmpLigne).Cells(cmpColone).Value = Dr(cmpColone)
                                End If
                            Next

                            'With cheq
                            '    .idPai = Dr.GetInt32(0)
                            '    .numChe = Dr.GetString(1)
                            '    .nominatif = Dr.GetBoolean(2)
                            '    .dteRetra = Dr(3)
                            '    .dateChequ = Dr(4)
                            '    .banque = Dr(5)
                            'End With
                            cmpLigne += 1
                        End While
                    End If
                End If
                Dr.Close()
                cnClose()
            Else
                MsgBox("Vous devez remplir un filtre!!!", MsgBoxStyle.Critical, "Erreur")
            End If
        Catch ex As Exception
            MsgBox("Une erreur c'est produite, veuillez informer l'administrateur de l'application !!!" & Environment.NewLine & "Déscription de l'erreur :" & Environment.NewLine & ex.Message, MsgBoxStyle.Critical, "Erreur")
        End Try
    End Sub

    Private Sub frmGestionCheque_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Try
            cmbEtat.SelectedIndex = 0
            cmbTypePaiement.SelectedIndex = 0
            txtAnnee.Text = Now.Year
        Catch ex As Exception
            MsgBox("Une erreur c'est produite, veuillez informer l'administrateur de l'application !!!" & Environment.NewLine & "Déscription de l'erreur :" & Environment.NewLine & ex.Message, MsgBoxStyle.Critical, "Erreur")
        End Try
    End Sub
    Public Sub clearFiltre()
        txtAnnee.Clear()
        txtMois.Clear()
        txtJour.Clear()
    End Sub
    Private Sub cmbTypePaiement_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmbTypePaiement.SelectedIndexChanged
        clearFiltre()
        Try
            Select Case cmbTypePaiement.SelectedIndex
                Case 0
                    pnlPersonne.Text = "Date chéque Par :"
                    DataGridView1.Rows.Clear()
                    'reformater les contrôles
                    'Datagrid
                    With DataGridView1
                        .Columns.Clear()
                        .ColumnCount = 8
                        .Columns(0).Visible = False
                        .Columns(1).Name = "N° Cheque"
                        .Columns(1).Width = 114
                        .Columns(2).Name = "Nominatif"
                        .Columns(3).Name = "Date retrait"
                        .Columns(3).Width = 125
                        .Columns(4).Name = "Date chéque"
                        .Columns(4).Width = 126
                        .Columns(5).Name = "Banque destinateur"
                        .Columns(6).Name = "Banque destinataire"
                        .Columns(7).Visible = False
                        .Size = New System.Drawing.Size(600, 215)
                        .Location = New System.Drawing.Point(100, 333)
                    End With
                    cmbEtat.Enabled = True
                Case 1
                    pnlPersonne.Text = "Date effet Par :"
                    With DataGridView1
                        .Columns.Clear()
                        .ColumnCount = 9
                        .Columns(0).Visible = False
                        .Columns(1).Name = "N° Effet"
                        .Columns(5).Name = "Nominatif"

                        .Columns(2).Name = "Date d'effet"
                        .Columns(2).Width = 125

                        .Columns(4).Name = "Date d'échéance"
                        .Columns(4).Width = 125

                        .Columns(3).Name = "Date retrait"
                        .Columns(3).Width = 125

                        .Columns(6).Name = "Banque destinataire"
                        .Columns(7).Name = "Banque destinateur"
                        .Size = New System.Drawing.Size(695, 215)
                        .Location = New System.Drawing.Point(40, 333)
                        .Columns(8).Visible = False
                    End With
                    cmbEtat.Enabled = True
                Case Else
                    pnlPersonne.Text = "Date virement Par :"
                    With DataGridView1
                        .Columns.Clear()
                        .ColumnCount = 7
                        .Columns(0).Visible = False
                        .Columns(1).Name = "N° Compte"
                        .Columns(1).Width = 125
                        .Columns(2).Name = "Banque destinataire"
                        .Columns(2).Width = 125

                        .Columns(3).Name = "Banque destinateur"
                        .Columns(3).Width = 125

                        .Columns(4).Name = "Date virement"
                        .Columns(4).Width = 125

                        .Columns(5).Name = "Nom"
                        .Columns(5).Width = 125

                        .Size = New System.Drawing.Size(645, 215)
                        .Location = New System.Drawing.Point(70, 333)
                        .Columns(6).Visible = False
                    End With
                    cmbEtat.Enabled = False
            End Select
        Catch ex As Exception
            MsgBox("Une erreur c'est produite, veuillez informer l'administrateur de l'application !!!" & Environment.NewLine & "Déscription de l'erreur :" & Environment.NewLine & ex.Message, MsgBoxStyle.Critical, "Erreur")
        End Try
    End Sub
    Dim row As DataGridViewRow
    Private Sub DataGridView1_DoubleClick(ByVal sender As Object, ByVal e As System.EventArgs) Handles DataGridView1.DoubleClick
        Try
            If DataGridView1.SelectedRows.Count > 0 Then
                If Not DataGridView1.SelectedRows(0).Cells Is Nothing Then
                    row = DataGridView1.SelectedRows(0)
                    Dim k As Integer
                    Select Case cmbTypePaiement.SelectedIndex
                        Case 0
                            k = 7
                        Case 1
                            k = 8
                        Case 2
                            k = 7
                    End Select
                    If Not row.Cells(k).Value Is Nothing Then
                        'S'il s'agit d'un parraineur alors il faut se rendre à la form de paiement de ces derniers
                        If row.Cells(k).Value = "P" Then

                            StandardInitial()
                            frmMesParraines.MdiParent = Frm_Main
                            frmMesParraines.Show()
                            frmMesParraines.Visible = False

                            'Requète pour avoir le parainneur et le client à partir d'un idPai
                            Cmd = New SqlCommand("select pc.idClt , pc.idParai from paiementParrain pp " &
                                                "inner join SommeParain sp " &
                                                "on pp.idParrain = sp.idParain " &
                                                "inner join paraiClt pc " &
                                                "on pc.idParainage = sp.idParain " &
                                                "where idPai = " & row.Cells(0).Value, Con)
                            open()
                            Dr = Cmd.ExecuteReader
                            If Dr.HasRows Then
                                Dr.Read()
                                idClt = Dr.GetInt32(0)
                                For Each it As DictionaryEntry In frmMesParraines.cmbParrin.Items
                                    If it.Key = Dr.GetInt32(1) Then
                                        Dr.Close()
                                        cnClose()
                                        frmMesParraines.cmbParrin.SelectedItem = it
                                        Exit For
                                    End If
                                Next

                                For Each lstItem As ListViewItem In frmMesParraines.lstViwClt.Items
                                    If CInt(lstItem.SubItems(0).Text) = idClt Then
                                        lstItem.Selected = True
                                        lstItem.Focused = True
                                        idClt = 0
                                        Exit For
                                    End If
                                Next
                            End If
                            frmMesParraines.Visible = True
                            Me.Close()
                        ElseIf row.Cells(k).Value = "F" Then
                            StandardInitial()
                            frmServicesFrn.MdiParent = Frm_Main
                            frmServicesFrn.Show()
                            frmServicesFrn.Visible = False


                            'Requète pour avoir le service du fournisseur à partir d'un idPai
                            Cmd = New SqlCommand("select sf.idServ,sf.idFr from paiementFour pf " &
                                                "inner join	serviceFour sf " &
                                                "on pf.idServ = sf.idServ " &
                                                "where idPai = " & row.Cells(0).Value, Con)
                            open()
                            Dr = Cmd.ExecuteReader
                            If Dr.HasRows Then
                                Dr.Read()
                                idClt = Dr.GetInt32(0)
                                For Each it As DictionaryEntry In frmServicesFrn.cmbFour.Items
                                    If it.Key = Dr.GetInt32(1) Then
                                        Dr.Close()
                                        cnClose()
                                        frmServicesFrn.cmbFour.SelectedItem = it
                                        Exit For
                                    End If
                                Next

                                For Each lstItem As ListViewItem In frmServicesFrn.lstViwSrv.Items
                                    If CInt(lstItem.SubItems(0).Text) = idClt Then
                                        lstItem.Selected = True
                                        lstItem.Focused = True
                                        idClt = 0
                                        Exit For
                                    End If
                                Next
                            End If
                            frmServicesFrn.Visible = True
                            Me.Close()
                        End If
                    End If
                End If
            End If
        Catch ex As Exception
            MsgBox("Une erreur c'est produite, veuillez informer l'administrateur de l'application !!!" & Environment.NewLine & "Déscription de l'erreur :" & Environment.NewLine & ex.Message, MsgBoxStyle.Critical, "Erreur")
        End Try
    End Sub

    Private Sub btnModif_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnModif.Click
        Try
            If cmbTypePaiement.SelectedIndex <> 2 Then
                If DataGridView1.SelectedRows.Count > 0 Then
                    If Not DataGridView1.SelectedRows(0).Cells Is Nothing Then
                        row = DataGridView1.SelectedRows(0)
                        Dim recherche As String = InputBox("Entrer la date de rerait", "Modifier date Retrait", "", , )
                        If Not String.IsNullOrEmpty(recherche) Then
                            If regDate(recherche) Then
                                If cmbTypePaiement.SelectedIndex = 0 Then
                                    Cmd = New SqlCommand("update cheques set dteRetra = '" & Format(Date.Parse(recherche), "MM-dd-yyyy") & "' where idPai = " & row.Cells(0).Value.ToString, Con)
                                Else
                                    Cmd = New SqlCommand("update Effet set dteRetrait = '" & Format(Date.Parse(recherche), "MM-dd-yyyy") & "' where idPai = " & row.Cells(0).Value.ToString, Con)
                                End If
                                open()
                                Cmd.ExecuteNonQuery()
                                row.Cells(3).Value = recherche
                            Else
                                MsgBox("La date est incorrecte!!! elle doit être au format jj/mm/aaaa", MsgBoxStyle.Critical, "Erreur")
                            End If
                        End If
                End If
            Else
                MsgBox("Vous devez séléctionné une ligne!!!", MsgBoxStyle.Critical, "Erreur")
            End If
            End If
        Catch ex As Exception
            MsgBox("Une erreur c'est produite, veuillez informer l'administrateur de l'application !!!" & Environment.NewLine & "Déscription de l'erreur :" & Environment.NewLine & ex.Message, MsgBoxStyle.Critical, "Erreur")
        End Try
    End Sub
End Class